var express = require('express');
var fs = require('fs');
var mysql = require('mysql');
var ejs = require('ejs');
var router = express.Router();

//连接MySQL数据库
var client = mysql.createConnection({
  host: '127.0.0.1',
  port:"3306",
  user: 'root',
  password: '123456',
  database: 'library'
});
//判断数据库是否连接成功
client.connect(function(err){
  if (err){
      console.log('[query] - :'+err);
      return;
  }
  console.log('[connection connect]  MySQL数据库连接成功!');
});

//显示用户列表
router.get('/', function (request, response) {
  console.log(request.body);
  client.query('select * from user', function (error, results) {
      response.render('user/user-list.ejs', {
          data: results
      });
  });
});

//搜索用户
router.get('/search/:keyword', function (request, response) {
  const keyword = request.params.keyword;
  console.log(request.params);
  //读取模板文件
  if (keyword ) {
   try {
        const query = 'SELECT * FROM user WHERE username LIKE ?';
        const values = [`%${keyword}%`];
        client.query(query, values,function (error, results) {
             if (error) {
                 console.error(error);
                 response.status(500).send('Internal Server Error');
                 return;
             }
             console.log(results);
             response.send(results );
        });
   } catch (error) {
        console.error(error);
        response.status(500).send('Internal Server Error');
    }
  }
});


//添加用户页面
router.get('/add', function (request, response) {
  //读取模板文件
  response.render('user/user-add.ejs');
});

//添加用户处理逻辑
router.post('/add', function (request, response) {
  //声明body
  var body = request.body;
  console.log(body)
  //执行SQL语句
  client.query('insert into user (username, password,nikename) VALUES (?,?,?)', [
    body.username, body.password,body.username
    ],function () {
       //响应信息
       response.redirect('/user');
  });
});

router.get('/edit/:id', function (request, response) {
  console.log(request.params.id);
  client.query('select * from user where id =?', [
      request.params.id
  ], function (error, result) {
      console.log(result);
      response.render('user/user-edit.ejs', {
          data: result[0]
      });
  });
});


router.post('/edit/:id', function (request, response) {
  //声明body
  var body = request.body;
  console.log(body);
  //执行SQL语句
  client.query('update user set nikename=? where username=?',
     [body.nikename, body.username], function () {
       //响应信息
       response.redirect('/user');
  });
});

router.get('/delete/:id', function (request, response) {
  //执行SQL语句
  client.query('delete from user where id=?', [request.params.id], function () {
       //响应信息
       response.redirect('/user');
  });
});

module.exports = router;